Use master
Go
Drop Database MyMenu
Go
Create database MyMenu
Go
Use MyMenu
Go

Create Table Member
(
ID int identity(1,1) primary key not null,
Name varchar(20) not null,
Pass varchar(20) not null,
Inscription Date null,
Actif bit not null,
Mail varchar(100) not null
)

Create Table Menu
(
ID int identity(1,1) primary key not null,
ID_Member int,
Title varchar(50),
Creation Date,
Img varchar(50),
ID_Day int
)

Create Table Recette
(
ID int identity(1,1) primary key not null,
Title varchar(50),
ID_Member int,
Img varchar(50),
Value Text
)

Create Table MenuRecette
(
ID_Recette int primary key,
ID_Menu int
)

Create table Aliment
(
ID int identity(1,1) primary key not null,
Name varchar(50),
ID_TypeAliment int,
Img varchar(50)
)



Create Table RecetteAliment
(
ID_Recette int primary key not null,
ID_Aliment int not null,
Quantite float,
ID_TypeQuantite int
)

Create Table TypeQuantite
(
ID int identity(1,1) primary key not null,
Name varchar(20),
ShortName varchar(10)
)

Create table TypeAliment
(
ID int identity(1,1) primary key not null,
Name varchar(50)
)

Create table Commentaire
(
ID int identity(1,1) primary key not null,
ID_Recette int,
ID_Member int,
ID_Menu int,
Value Text,
PostDate Date,
Idx int
)

Create Table Details
(
ID int identity(1,1) primary key not null,
ID_Recette int,
ID_Menu int,
Value Text,
Idx int
)

Create Table Semaine
(
ID int identity(1,1) primary key not null,
Name varchar(50)
)

Create Table Day
(
ID int identity(1,1) primary key not null,
Name varchar(20),
ID_Semaine int
)



Alter table Commentaire Add CONSTRAINT FK_Recette_Commentaire FOREIGN KEY (ID_Recette) REFERENCES Recette(ID);
Alter table Commentaire Add CONSTRAINT FK_Menu_Commentaire FOREIGN KEY (ID_Menu) REFERENCES Menu(ID);
Alter table Commentaire Add CONSTRAINT FK_Member_Commentaire FOREIGN KEY (ID_Member) REFERENCES Member(ID);
Alter table RecetteAliment Add CONSTRAINT FK_Recette_RecetteAliment FOREIGN KEY (ID_Recette) REFERENCES RECETTE(ID);
Alter table RecetteAliment Add CONSTRAINT FK_Aliment_RecetteAliment FOREIGN KEY (ID_Aliment) REFERENCES ALIMENT(ID);
Alter table RecetteAliment Add CONSTRAINT FK_TypeQuantite_RecetteAliment FOREIGN KEY (ID_TypeQuantite) REFERENCES TypeQuantite(ID);
Alter table Aliment Add CONSTRAINT FK_TypeAliment_Aliment FOREIGN KEY (ID_TypeAliment) REFERENCES TypeAliment(ID);
Alter table Recette Add CONSTRAINT FK_Member_Recette FOREIGN KEY (ID_Member) REFERENCES Member(ID);
Alter table MenuRecette Add CONSTRAINT FK_Recette_MenuRecette FOREIGN KEY (ID_Recette) REFERENCES Recette(ID);
Alter table MenuRecette Add CONSTRAINT FK_Menu_MenuRecette FOREIGN KEY (ID_Menu) REFERENCES Menu(ID);
Alter table Day Add CONSTRAINT FK_Semaine_Day FOREIGN KEY (ID_Semaine) REFERENCES Semaine(ID);
Alter table Menu Add CONSTRAINT FK_Day_Menu FOREIGN KEY (ID_Day) REFERENCES Day(ID);
Alter table Details Add CONSTRAINT FK_Recette_Details FOREIGN KEY (ID_Recette) REFERENCES Recette(ID);
Alter table Details Add CONSTRAINT FK_Menu_Details FOREIGN KEY (ID_Menu) REFERENCES Menu(ID);